package com.jandar.util;

import com.cl.mvc.util.DbUtil;
import com.jandar.entity.BillExcelSource;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.core.types.dsl.Expressions;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaBuilder;
import javax.transaction.Transactional;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Component
public class DynamicQueryUtil {


    @Autowired
    private DbUtil dbUtil;
    @Autowired
    private JdbcTemplateUtil jdbcTemplateUtil;
    @PersistenceContext
    private EntityManager entityManager;


    public List<Map<String,String>> listField(String tableName) {
        return dbUtil.listField(tableName);
    }
    public static String replacePlaceholders(String template, Map<String, String> paramMap) {
        Pattern pattern = Pattern.compile("\\$\\{([^:]+)(?::(.*))?}");
        Matcher matcher = pattern.matcher(template);

        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            String key = matcher.group(1);
            if (!paramMap.containsKey(key) && matcher.group(2) == null) {
                throw new RuntimeException("缺少参数：" + key);
            }
            String paramValue = paramMap.getOrDefault(key, matcher.group(2));
            matcher.appendReplacement(sb, Matcher.quoteReplacement(paramValue)); // 使用quoteReplacement以防止特殊字符转义问题
        }
        matcher.appendTail(sb);

        return sb.toString();
    }

    public static String generateOrderFields(Map<String, Object> order) {
        StringBuilder stringBuilder = new StringBuilder(" ");
        if (order != null && !order.isEmpty()) {
            stringBuilder.append(" order by ");
            for (Map.Entry<String, Object> entry : order.entrySet()) {
                stringBuilder.append(entry.getKey()).append(" ").append(entry.getValue()).append(",");
            }
            stringBuilder.deleteCharAt(stringBuilder.length() - 1);
        }
        return stringBuilder.toString();
    }

    public static String generateCondition(Map<String, Object> condition) {
        StringBuilder stringBuilder = new StringBuilder(" 1=1 ");
        if (condition != null) {
            for (Map.Entry<String, Object> entry : condition.entrySet()) {
                String key = entry.getKey();
                Object value = entry.getValue();
                stringBuilder.append(" and ");
                if (value instanceof List) {
                    List<Object> values = (List<Object>) value;
                    if (key.endsWith("_in")) {
                        key = key.replace("_in", "");
                        stringBuilder.append(key).append(" in (").append(StringUtils.join(values, ",")).append(")");
                    }
                    if (values.size() == 2) {
                        stringBuilder.append(key).append(" between ").append(values.get(0)).append(" and ").append(values.get(1));
                    }
                } else if (key.endsWith("_equal")) {
                    key = key.replace("_equal", "");
                    stringBuilder.append(key).append(" = ").append(value);
                } else if (key.endsWith("_start")) {
                    key = key.replace("_start", "");
                    stringBuilder.append(key).append(" >= ").append(value);
                } else if (key.endsWith("_end")) {
                    key = key.replace("_end", "");
                    stringBuilder.append(key).append(" <= ").append(value);
                } else {
                    stringBuilder.append(key).append(" like %").append(value).append("%");

                }
            }
        }
        return stringBuilder.toString();
    }

    @Transactional
    public void execute(String sql) {
        javax.persistence.Query nativeQuery = entityManager.createNativeQuery(sql);
        nativeQuery.executeUpdate();
    }

    public List<Map<String, Object>> query(String sql) {
        List<Map<String, Object>> res = jdbcTemplateUtil.queryForList(sql);
        return res;
    }

    public Long queryCount(String tableName, Map<String, Object> condition) {
        String sql = "select count(1) from " + tableName + " where " + generateCondition(condition);
        javax.persistence.Query nativeQuery = entityManager.createNativeQuery(sql);
        Long count = Long.parseLong(nativeQuery.getSingleResult().toString());
        return count;
    }

    public Long queryCount(String tableName) {
        String sql = "select count(1) from " + tableName;
        javax.persistence.Query nativeQuery = entityManager.createNativeQuery(sql);
        Long count = Long.parseLong(nativeQuery.getSingleResult().toString());
        return count;
    }

    public List<Map<String, Object>> queryAll(String tableName) {
        String sql = "SELECT * FROM " + tableName;
        javax.persistence.Query nativeQuery = entityManager.createNativeQuery(sql, Map.class);
        List<Map<String, Object>> results = nativeQuery.getResultList();
        return results;
    }

    public List<Map<String, Object>> queryAll(String tableName, String columnName) {
        String sql = "SELECT " + columnName + " FROM " + tableName;
        javax.persistence.Query nativeQuery = entityManager.createNativeQuery(sql, Map.class);
        List<Map<String, Object>> results = nativeQuery.getResultList();

        return results;
    }

    public List<Map<String, Object>> query(String tableName, Map<String, String> columns, Map<String, Object> condition, Map<String, Object> orderFields, Long offset, Long size) {
        String columnName = String.join(",", columns.keySet());
        String sql = "SELECT " + columnName + " FROM " + tableName + " WHERE " + generateCondition(condition) + generateOrderFields(orderFields);
        if (offset != null && size != null) {
            sql += " limit " + offset + "," + size;
        }
        javax.persistence.Query nativeQuery = entityManager.createNativeQuery(sql);
        List<Map<String, Object>> results = new ArrayList<>();
        List<Object[]> nativeQueryResultList = nativeQuery.getResultList();

        List<String> comments = new ArrayList<>(columns.values());
        nativeQueryResultList.forEach(item -> {

            Map<String, Object> map = new HashMap<>();
            for (int i = 0; i < item.length; i++) {
                map.put(comments.get(i), item[i]);
            }
            results.add(map);
        });

        return results;
    }
}